
package com.compuvest.webstat

import groovy.sql.Sql
import javax.naming.InitialContext

class MySQLService {

    def sql

    def MySQLService() {

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)
    }

    def getEpyUsers() {
//SELECT GROUP_CONCAT(CONCAT('{id:',user_id,',name:',QUOTE(name),'}')) json FROM sklad.users WHERE epy=true\G

        def users
        sql.eachRow(
            """SELECT CAST(GROUP_CONCAT(
            CONCAT(
            '{id:',user_id,
            ',name:',QUOTE(name),
            '}')) AS CHAR) json
            FROM sklad.users
            WHERE epy=1""")
        { row ->
            users = row.json
        }
        sql.close()

        return "{users:[${users ? users : '{}'}]}"
    }

    def getGoogleItemsStatus() {

        def status = []
        sql.eachRow(
            """SELECT CAST(CONCAT('{name:',QUOTE(iv.name),',cnt:',COUNT(*),'}') AS CHAR) json
            FROM sklad.itemsOnGoogle ig
            LEFT JOIN sklad.items it USING(item_id)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            WHERE ig.active=1
            GROUP BY it.vendor_id
            ORDER BY iv.name""")
        { row ->
            status.add(row.json)
        }
        sql.close()

        return "{status:[${status.size() ? status.join(',') : '{}'}]}"
    }

    def getEpyGroups(userID) {
//SELECT CAST(IFNULL(GROUP_CONCAT(CONCAT('{gid:',group_id,',uid:',user_id,',name:',QUOTE(name),',act:',active,'}')), '{}') AS CHAR) json FROM advert.epyUsersGroup WHERE user_id=5;

        def groups
        sql.eachRow(
            """SELECT CAST(GROUP_CONCAT(
            CONCAT(
            '{id:',group_id,
            ',uid:',user_id,
            ',name:',QUOTE(name),
            ',act:',active,
            '}')) AS CHAR) json
            FROM advert.epyUsersGroup
            WHERE user_id=?""", [userID])
        { row ->
            groups = row.json
        }
        sql.close()

        return "{groups:[${groups ? groups : '{}'}]}"
    }

    def editEpyGroup(userid, groupid) {
//SELECT CAST(IFNULL(GROUP_CONCAT(CONCAT('{gid:',group_id,',uid:',user_id,',name:',QUOTE(name),',act:',active,'}')), '{}') AS CHAR) json FROM advert.epyUsersGroup WHERE user_id=5;

        def group
        sql.eachRow(
            """SELECT CAST(GROUP_CONCAT(
            CONCAT(
            '{gid:',group_id,
            ',uid:',user_id,
            ',name:',QUOTE(name),
            ',active:',active,
            '}')) AS CHAR) json
            FROM advert.epyUsersGroup
            WHERE group_id=?""", [groupid])
        { row ->
            group = row.json
        }
        sql.close()

        return """{group:${group ? group : "{gid: 0, uid: ${userid}, name: '', active: 0}"}}"""
    }

    def createEpyGroup(userid, cb) {

        sql.execute("INSERT INTO advert.epyUsersGroup SET user_id=?,${cb}", [userid])
        sql.close()
    }

    def updateEpyGroup(userid, groupid, cb) {

        sql.execute("UPDATE advert.epyUsersGroup SET user_id=?,${cb} WHERE group_id=?", [userid, groupid])
        sql.close()
    }

    def deleteEpyGroup(groupid) {

        sql.execute("DELETE FROM advert.epyUsersGroup WHERE group_id=?", [groupid])
        sql.close()

        // and delete all items this group
        sql.execute("DELETE FROM advert.epyUserItems WHERE group_id=?", [groupid])
        sql.close()
    }

    def getEpyItems(groupid) {

        def items
        sql.eachRow(
            """SELECT CAST(GROUP_CONCAT(
            CONCAT(
            '{id:',it.item_id,
            ',sku:',QUOTE(it.accwareCatID),
            ',name:',QUOTE(it.name),
            ',qty:',it.stock_quantity,

            ',send:',ui.send,

            ',amazonQty:',ui.amazonQty,
            ',amazonPrice:',ui.amazonPrice,

            ',amzcompQty:',ui.amzcompQty,
            ',amzcompPrice:',ui.amzcompPrice,

            ',ebayQty:',ui.ebayQty,
            ',ebayPrice:',ui.ebayPrice,

            ',ebayAliQty:',ui.ebayAliQty,
            ',ebayAliPrice:',ui.ebayAliPrice,

            ',buycomQty:',ui.buycomQty,
            ',buycomPrice:',ui.buycomPrice,
            '}')) AS CHAR) json
            FROM advert.epyUserItems ui
            LEFT JOIN sklad.items it USING(item_id)
            WHERE ui.group_id=?""", [groupid])
        { row ->
            items = row.json
        }
        sql.close()

        return "{items:[${items ? items : '{}'}]}"
    }

    def getEpyItem(itemid) {

        def item
        sql.eachRow(
            """SELECT CAST(
            CONCAT(
            '{id:',it.item_id,
            ',sku:',QUOTE(it.accwareCatID),
            ',name:',QUOTE(it.name),
            ',costPrice:',it.cost_price,
            ',sellPrice:',it.sell_price,
            ',qty:',it.stock_quantity,

            ',send:',ui.send,

            ',amazonQty:',ui.amazonQty,
            ',amazonPrice:',ui.amazonPrice,

            ',amzcompQty:',ui.amzcompQty,
            ',amzcompPrice:',ui.amzcompPrice,

            ',ebayQty:',ui.ebayQty,
            ',ebayPrice:',ui.ebayPrice,

            ',ebayAliQty:',ui.ebayAliQty,
            ',ebayAliPrice:',ui.ebayAliPrice,

            ',buycomQty:',ui.buycomQty,
            ',buycomPrice:',ui.buycomPrice,
            '}') AS CHAR) json
            FROM advert.epyUserItems ui
            LEFT JOIN sklad.items it USING(item_id)
            WHERE ui.item_id=?""", [itemid])
        { row ->
            item = row.json
        }
        sql.close()

        if(!item) {
            item = "{id:0,sku:'',name:'',costPrice:0,sellPrice:0,qty:0,send:0," +
            "amazonQty:0,amazonPrice:0,amzcompQty:0,amzcompPrice:0,ebayQty:0,ebayPrice:0,ebayAliQty:0,ebayAliPrice:0,buycomQty:0,buycomPrice:0}"
        }

        return "{item:${item}}"
    }

    def searchEpyItem(sku) {

        def item
        sql.eachRow(
            """SELECT CAST(CONCAT('{id:',item_id,',sku:',QUOTE(accwareCatID),'}') AS CHAR) json
            FROM sklad.items
            WHERE accwareCatID=?""", [sku])
        { row ->
            item = row.json
        }
        sql.close()

        return "{item:${item ? item : '{id:0}'}}"
    }

    def insertEpyItem(itemid, groupid, sku) {

        sql.execute("INSERT INTO advert.epyUserItems SET group_id=?,item_id=?,accwareCatID=?",[groupid, itemid, sku])
        sql.close()
    }

    def updateEpyItem(itemid, cb) {

        sql.executeUpdate("UPDATE advert.epyUserItems SET ${cb} WHERE item_id=?", [itemid])
        sql.close()
    }

    def deleteEpyItem(itemid) {

        sql.execute("DELETE FROM advert.epyUserItems WHERE item_id=?", [itemid])
        sql.close()
    }

    def getEpyTestItem(sku) {

        def item
        sql.eachRow(
            """SELECT CAST(CONCAT(
            '{id:',it.item_id,
            ',sku:',QUOTE(it.accwareCatID),
            ',name:',QUOTE(it.name),
            ',catname:',QUOTE(IFNULL(gc.name,'')),
            ',costprice:',it.cost_price,
            ',sellprice:',it.sell_price,
            ',hotprice:',it.hot_price,
            ',stock:',IF(it.stock='Y',1,0),
            ',qty:',it.stock_quantity,
            ',created:',QUOTE(it.createTime),
            ',updated:',QUOTE(it.updateTime),
            ',active:',IF(it.active = 'Y',1,0),

            ',groupname:',QUOTE(IFNULL(aeg.name,'')),
            ',groupactive:',IFNULL(aeg.active,0),
            ',grpname:',QUOTE(IFNULL(aeg.name,'')),
            ',gactive:',IFNULL(aeg.active,0),

            ',vid:',it.vendor_id,
            ',vendname:',QUOTE(iv.name),

            ',vactive:',IFNULL(av.active,0),

            ',purchasesend:',IFNULL(eui.send,0),
            ',purchaseusername:',QUOTE(IFNULL(sus.name,'')),
            ',purchasegroupname:',QUOTE(IFNULL(eug.name,'')),

            ',google_action:',QUOTE(itg.actionToGoogle),
            ',google_update:',QUOTE(itg.updated),

            ',older:',
                '[{day:"0_30",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) BETWEEN 0  AND 30,1),'}',
                ',{day:"31_60",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) BETWEEN 31 AND 60,0),'}',
                ',{day:"61_90",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) BETWEEN 61 AND 90,0),'}',
                ',{day:"91_180",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) BETWEEN 91 AND 180,0),'}',
                ',{day:"181_240",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) BETWEEN 181 AND 240,0),'}',
                ',{day:"241_more",active:' ,IFNULL(DATEDIFF(NOW(),it.stockDate) >= 241,0),'}',
            ']'

            ',advert:[',
                '{name:"Amazon"',
                    ',groups:',IFNULL(CONCAT(
                        '[{name:"0_30",price:',it.sell_price,',defQty:',aeg.amazon_defQty0_30,',minQty:',aeg.amazon_minQty0_30,',margin:',aeg.amazon_margin0_30,',charge:',aeg.amazon_charge0_30,'}',
                        ',{name:"31_60",price:',it.sell_price,',defQty:',aeg.amazon_defQty31_60,',minQty:',aeg.amazon_minQty31_60,',margin:',aeg.amazon_margin31_60,',charge:',aeg.amazon_charge31_60,'}',
                        ',{name:"61_90",price:',it.hot_price,',defQty:',aeg.amazon_defQty61_90,',minQty:',aeg.amazon_minQty61_90,',margin:',aeg.amazon_margin61_90,',charge:',aeg.amazon_charge61_90,'}',
                        ',{name:"91_180",price:',it.hot_price,',defQty:',aeg.amazon_defQty91_180,',minQty:',aeg.amazon_minQty91_180,',margin:',aeg.amazon_margin91_180,',charge:',aeg.amazon_charge91_180,'}',
                        ',{name:"181_240",price:',it.hot_price,',defQty:',aeg.amazon_defQty181_240,',minQty:',aeg.amazon_minQty181_240,',margin:',aeg.amazon_margin181_240,',charge:',aeg.amazon_charge181_240,'}',
                        ',{name:"241_more",price:',it.hot_price,',defQty:',aeg.amazon_defQty241_more,',minQty:',aeg.amazon_minQty241_more,',margin:',aeg.amazon_margin241_more,',charge:',aeg.amazon_charge241_more,'}',
                        ']'
                    ),'""'),
                   ',vendor:' ,IFNULL(CONCAT(
                        '{send:',avc.amazonSend,',qty:',av.amazonQty,',charge:',av.amazonCharge,',margin:',av.amazonMargin,'}'
                    ),'""'),
                    ',purchase:',IFNULL(CONCAT(
                        '{qty:',eui.amazonQty,',price:',eui.amazonPrice,',pass:0}'
                    ),'{pass:1}'),
                '}',

                ',{name:"AmzComp"',
                    ',groups:',IFNULL(CONCAT(
                        '[{name:"0_30",price:',it.sell_price,',defQty:',aeg.amzcomp_defQty0_30,',minQty:',aeg.amzcomp_minQty0_30,',margin:',aeg.amzcomp_margin0_30,',charge:',aeg.amzcomp_charge0_30,'}',
                        ',{name:"31_60",price:',it.sell_price,',defQty:',aeg.amzcomp_defQty31_60,',minQty:',aeg.amzcomp_minQty31_60,',margin:',aeg.amzcomp_margin31_60,',charge:',aeg.amzcomp_charge31_60,'}',
                        ',{name:"61_90",price:',it.hot_price,',defQty:',aeg.amzcomp_defQty61_90,',minQty:',aeg.amzcomp_minQty61_90,',margin:',aeg.amzcomp_margin61_90,',charge:',aeg.amzcomp_charge61_90,'}',
                        ',{name:"91_180",price:',it.hot_price,',defQty:',aeg.amzcomp_defQty91_180,',minQty:',aeg.amzcomp_minQty91_180,',margin:',aeg.amzcomp_margin91_180,',charge:',aeg.amzcomp_charge91_180,'}',
                        ',{name:"181_240",price:',it.hot_price,',defQty:',aeg.amzcomp_defQty181_240,',minQty:',aeg.amzcomp_minQty181_240,',margin:',aeg.amzcomp_margin181_240,',charge:',aeg.amzcomp_charge181_240,'}',
                        ',{name:"241_more",price:',it.hot_price,',defQty:',aeg.amzcomp_defQty241_more,',minQty:',aeg.amzcomp_minQty241_more,',margin:',aeg.amzcomp_margin241_more,',charge:',aeg.amzcomp_charge241_more,'}',
                        ']'
                    ),'""'),
                    ',vendor:' ,IFNULL(CONCAT(
                        '{send:',avc.amzcompSend,',qty:',av.amzcompQty,',charge:',av.amzcompCharge,',margin:',av.amzcompMargin,'}'
                    ),'""'),
                    ',purchase:',IFNULL(CONCAT(
                        '{qty:',eui.amzcompQty,',price:',eui.amzcompPrice,',pass:0}'
                    ),'{pass:1}'),
                '}',

                ',{name:"Ebay"',
                    ',groups:',IFNULL(CONCAT(
                        '[{name:"0_30",price:',it.sell_price,',defQty:',aeg.ebay_defQty0_30,',minQty:',aeg.ebay_minQty0_30,',margin:',aeg.ebay_margin0_30,',charge:',aeg.ebay_charge0_30,'}',
                        ',{name:"31_60",price:',it.sell_price,',defQty:',aeg.ebay_defQty31_60,',minQty:',aeg.ebay_minQty31_60,',margin:',aeg.ebay_margin31_60,',charge:',aeg.ebay_charge31_60,'}',
                        ',{name:"61_90",price:',it.hot_price,',defQty:',aeg.ebay_defQty61_90,',minQty:',aeg.ebay_minQty61_90,',margin:',aeg.ebay_margin61_90,',charge:',aeg.ebay_charge61_90,'}',
                        ',{name:"91_180",price:',it.hot_price,',defQty:',aeg.ebay_defQty91_180,',minQty:',aeg.ebay_minQty91_180,',margin:',aeg.ebay_margin91_180,',charge:',aeg.ebay_charge91_180,'}',
                        ',{name:"181_240",price:',it.hot_price,',defQty:',aeg.ebay_defQty181_240,',minQty:',aeg.ebay_minQty181_240,',margin:',aeg.ebay_margin181_240,',charge:',aeg.ebay_charge181_240,'}',
                        ',{name:"241_more",price:',it.hot_price,',defQty:',aeg.ebay_defQty241_more,',minQty:',aeg.ebay_minQty241_more,',margin:',aeg.ebay_margin241_more,',charge:',aeg.ebay_charge241_more,'}',
                        ']'
                    ),'""'),
                    ',vendor:' ,IFNULL(CONCAT(
                        '{send:',avc.ebaySend,',qty:',av.ebayQty,',charge:',av.ebayCharge,',margin:',av.ebayMargin,'}'
                    ),'""'),
                    ',purchase:',IFNULL(CONCAT(
                        '{qty:',eui.ebayQty,',price:',eui.ebayPrice,',pass:0}'
                    ),'{pass:1}'),
                '}',

                ',{name:"EbayA"',
                    ',groups:',IFNULL(CONCAT(
                        '[{name:"0_30",price:',it.sell_price,',defQty:',aeg.ebayali_defQty0_30,',minQty:',aeg.ebayali_minQty0_30,',margin:',aeg.ebayali_margin0_30,',charge:',aeg.ebayali_charge0_30,'}',
                        ',{name:"31_60",price:',it.sell_price,',defQty:',aeg.ebayali_defQty31_60,',minQty:',aeg.ebayali_minQty31_60,',margin:',aeg.ebayali_margin31_60,',charge:',aeg.ebayali_charge31_60,'}',
                        ',{name:"61_90",price:',it.hot_price,',defQty:',aeg.ebayali_defQty61_90,',minQty:',aeg.ebayali_minQty61_90,',margin:',aeg.ebayali_margin61_90,',charge:',aeg.ebayali_charge61_90,'}',
                        ',{name:"91_180",price:',it.hot_price,',defQty:',aeg.ebayali_defQty91_180,',minQty:',aeg.ebayali_minQty91_180,',margin:',aeg.ebayali_margin91_180,',charge:',aeg.ebayali_charge91_180,'}',
                        ',{name:"181_240",price:',it.hot_price,',defQty:',aeg.ebayali_defQty181_240,',minQty:',aeg.ebayali_minQty181_240,',margin:',aeg.ebayali_margin181_240,',charge:',aeg.ebayali_charge181_240,'}',
                        ',{name:"241_more",price:',it.hot_price,',defQty:',aeg.ebayali_defQty241_more,',minQty:',aeg.ebayali_minQty241_more,',margin:',aeg.ebayali_margin241_more,',charge:',aeg.ebayali_charge241_more,'}',
                        ']'
                    ),'""'),
                    ',vendor:' ,IFNULL(CONCAT(
                        '{send:',avc.ebayaliSend,',qty:',av.ebayaliQty,',charge:',av.ebayaliCharge,',margin:',av.ebayaliMargin,'}'
                    ),'""'),
                    ',purchase:',IFNULL(CONCAT(
                        '{qty:',eui.ebayaliQty,',price:',eui.ebayaliPrice,',pass:0}'
                    ),'{pass:1}'),
                '}',

                ',{name:"Buycom"',
                    ',groups:',IFNULL(CONCAT(
                        '[{name:"0_30",price:',it.sell_price,',defQty:',aeg.buycom_defQty0_30,',minQty:',aeg.buycom_minQty0_30,',margin:',aeg.buycom_margin0_30,',charge:',aeg.buycom_charge0_30,'}',
                        ',{name:"31_60",price:',it.sell_price,',defQty:',aeg.buycom_defQty31_60,',minQty:',aeg.buycom_minQty31_60,',margin:',aeg.buycom_margin31_60,',charge:',aeg.buycom_charge31_60,'}',
                        ',{name:"61_90",price:',it.hot_price,',defQty:',aeg.buycom_defQty61_90,',minQty:',aeg.buycom_minQty61_90,',margin:',aeg.buycom_margin61_90,',charge:',aeg.buycom_charge61_90,'}',
                        ',{name:"91_180",price:',it.hot_price,',defQty:',aeg.buycom_defQty91_180,',minQty:',aeg.buycom_minQty91_180,',margin:',aeg.buycom_margin91_180,',charge:',aeg.buycom_charge91_180,'}',
                        ',{name:"181_240",price:',it.hot_price,',defQty:',aeg.buycom_defQty181_240,',minQty:',aeg.buycom_minQty181_240,',margin:',aeg.buycom_margin181_240,',charge:',aeg.buycom_charge181_240,'}',
                        ',{name:"241_more",price:',it.hot_price,',defQty:',aeg.buycom_defQty241_more,',minQty:',aeg.buycom_minQty241_more,',margin:',aeg.buycom_margin241_more,',charge:',aeg.buycom_charge241_more,'}',
                        ']'
                    ),'""'),
                    ',vendor:' ,IFNULL(CONCAT(
                        '{send:',avc.buycomSend,',qty:',av.buycomQty,',charge:',av.buycomCharge,',margin:',av.buycomMargin,'}'
                    ),'""'),
                    ',purchase:',IFNULL(CONCAT(
                        '{qty:',eui.buycomQty,',price:',eui.buycomPrice,',pass:0}'
                    ),'{pass:1}'),
                '}',
            ']'

            '}') AS CHAR) json
            FROM sklad.items it
            LEFT JOIN sklad.itemsOnGoogle itg USING(item_id)
            LEFT JOIN advert.epyUserItems eui USING(item_id)
            LEFT JOIN advert.epyUsersGroup eug ON eug.group_id=eui.group_id
            LEFT JOIN sklad.users sus ON sus.user_id=eug.user_id
            LEFT JOIN sklad.category gc USING(category_id)
            LEFT JOIN sklad.itemVendor iv USING(vendor_id)
            LEFT JOIN advert.epyGroupSubCategory aec USING(category_id)
            LEFT JOIN advert.epyGroups aeg ON aeg.group_id=aec.group_id
            LEFT JOIN advert.epyVendors av USING(vendor_id)
            LEFT JOIN advert.epyVendorSubCategories avc ON(avc.vendor_id=it.vendor_id AND avc.category_id=it.category_id)
            WHERE it.accwareCatID=?""", [sku])
        { row ->
            item = row.json
        }
        sql.close()

        return "{item:${item ? item : '{}'}}"
    }








}

